/****** Object:  Stored Procedure dbo.CGO_TriviaGetQuestion    Script Date: Monday, March 07, 2011 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'dbo.CGO_TriviaGetQuestion') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure dbo.CGO_TriviaGetQuestion
GO

/* -------------------------------------------------------------------------------------
/   AccountGet
/  ------------------------------------------------------------------------------------- */
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE dbo.CGO_TriviaGetQuestion	
  @EnrollID INT,
  @UserID INT  
AS
  IF NOT EXISTS ( SELECT  1
                  FROM    dbo.CGO_GameEnrollment M
                          INNER JOIN dbo.CGO_GameEnrollmentStatus S ON M.EnrollStatusID = S.StatusID
                  WHERE   EnrollmentID = @EnrollID
                          AND UserID = @UserID
                          AND StatusCode = 'PLAYING' )
    OR NOT EXISTS ( SELECT  1
                    FROM    dbo.CGO_TriviaResult
                    WHERE   GameEnrollmentID = @EnrollID
                            AND ResultStatusID = 1 ) 
  BEGIN
    RETURN
  END
  
  DECLARE @QuestionIndex INT
  
  SELECT  @QuestionIndex = MIN(QuestionIndex)
  FROM    dbo.CGO_TriviaResult
  WHERE   GameEnrollmentID = @EnrollID
          AND ResultStatusID = 1
          AND QuestionIndex > 0
  
  UPDATE  dbo.CGO_TriviaResult
  SET     TimeBegin = GETDATE() ,
          LastUpdateDate = GETDATE()
  WHERE   GameEnrollmentID = @EnrollID
          AND QuestionIndex = @QuestionIndex
          
  SELECT  M.QuestionIndex ,
          Q.CategoryID ,
          C.CategoryName ,
          M.QuestionID ,
          Q.Description ,
          Q.Difficulty,
          Q.Comment
  FROM    dbo.CGO_TriviaResult M
          INNER JOIN dbo.CGO_TriviaQuestion Q ON M.QuestionID = Q.QuestionID
          LEFT JOIN dbo.CGO_TriviaCategory C ON Q.CategoryID = C.CategoryID
  WHERE   M.GameEnrollmentID = @EnrollID
          AND QuestionIndex = @QuestionIndex
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO